<?php

/**
 * Created by Atom.
 * User: qiliping
 * Date: 2017/1/12
 * Time: 15:51
 */
class Classo2o_model extends CI_Model
{
  /**
  * 添加班级
  */
  public function add_class_by_school($school_id,$data)
  {
    //查询班主任、主讲老师
    $teacher  = $this->db->query("select teacher_name from {$this->db->dbprefix}o2o_teachers where id = {$data['manage_teacher_id']} limit 1")->row_array();
    if($teacher){
      $data['manage_teacher'] = $teacher['teacher_name'];
    }
    $teacher1  = $this->db->query("select teacher_name from {$this->db->dbprefix}o2o_teachers where id = {$data['lecturer_id']} limit 1")->row_array();
    if($teacher1){
      $data['lecturer_name'] = $teacher1['teacher_name'];
    }
    //查询课程
    $course = $this->db->query("select title from {$this->db->dbprefix}o2o_course where id={$data['course_id']}")->row_array();
    if($course){
      $data['course_name'] = $course['title'];
    }

    $data['school_id'] = $school_id;
    $data['sort'] = 0;
    $data['status'] = 1;
    $data['add_time'] = time();
    return $this->db->insert('o2o_class',$data);
  }

  /**
   * 获取最后的班级编号
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  function get_last_classnum($school_id)
  {
    $sql = "SELECT count(0) as class_count FROM {$this->db->dbprefix}o2o_class where school_id = '{$school_id}'order by id desc limit 1 ";
    $class = $this->db->query($sql)->row_array();
    if(empty($class)) return 0;
    else return $class['class_count'];
  }

  /**
   * 获取班级list
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_class_simple_by_school($school_id)
  {
    $sql = "SELECT id,class_name FROM {$this->db->dbprefix}o2o_class where school_id = '{$school_id}' and status=1";
    return $this->db->query($sql)->result_array();
  }

    /**
   * 根据学员获取班级list
   * @param  [type] $student_id [description]
   * @return [type]            [description]
   */
  public function get_class_simple_by_student($student_id)
  {
    $sql = "SELECT cla.id,cla.class_name FROM {$this->db->dbprefix}o2o_class_student stu left join {$this->db->dbprefix}o2o_class cla on cla.id = stu.class_id where stu.student_id = {$student_id}";
    return $this->db->query($sql)->result_array();
  }

  /**
   * 根据校区获取班级list
   * @param  [type] $school_campus_id [description]
   * @return [type]             [description]
   */
  public function get_class_simple_by_school_campus($school_campus_id)
  {
    $sql = "SELECT id,class_name FROM {$this->db->dbprefix}o2o_class where campus = '{$school_campus_id}' and status=1";
    return $this->db->query($sql)->result_array();
  }


  /**
  * 获取学校班级
  */
  public function get_class_by_school($limit = 0, $offset = 10, $conditions = array())
  {

    $where = "oc.school_id = '{$this->user_data['school_id']}' ";
    if($this->is_manage==0){
      $where .= " and oc.lecturer_id = '{$this->user_data['teacher_id']}'";
    }
    if(!empty($conditions['course_id'])){
      $where .= " and oc.course_id = '{$conditions['course_id']}'";
    }
    if(!empty($conditions['campus_id'])){
      $where .= " and oc.campus = '{$conditions['campus_id']}'";
    }
    if(!empty($conditions['class_name'])){
      $where .= " and oc.class_name LIKE '%{$conditions['class_name']}%'";
    }
    
    
    //（开课状态：1未开课2上课中3已结业）
    if(!empty($conditions['status'])){
      $status = $conditions['status'];
      if($status == 1){
        $where .= " and oc.start_time > curdate()";
      }else if($status==2){
        $where .= " and oc.start_time < curdate() and oc.end_time > curdate()";
      }else if($status==3){
        $where .= " and oc.end_time < curdate()";
      }
    }

    if(!empty($conditions['start_time'])){
        $where .= " and oc.start_time >= '{$conditions['start_time']}' ";
    }

    if(!empty($conditions['end_time'])){
        $where .= " and oc.start_time <= '{$conditions['end_time']}' ";
    }
    
    $sql = "SELECT SQL_CALC_FOUND_ROWS oc.*,cam.name as school_name,room.name as class_room FROM {$this->db->dbprefix}o2o_class oc ".
    " left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = oc.campus ".
    " left join {$this->db->dbprefix}o2o_classroom room on room.id = oc.classroom ".
    " where {$where} order by id desc";

    if($limit > 0 || $offset > 0){
          $sql .= " LIMIT $limit, $offset";
      }


    $result = $this->db->query($sql)->result_array();
    $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
    return array("count" => $row_num['row_num'], "result" => $result);
  }

  /**
   * 获取线下课程对应的班级
   * @param  [type] $course_id [description]
   * @return [type]            [description]
   */
  public function get_class_by_school_course($course_id)
  {
    $sql = "SELECT oc.*,cam.name as school_name,room.name as class_room FROM {$this->db->dbprefix}o2o_class oc ".
    " left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = oc.campus ".
    " left join {$this->db->dbprefix}o2o_classroom room on room.id = oc.classroom ".
    " where oc.course_id = '{$course_id}' order by id desc";

    return $this->db->query($sql)->result_array();
  }

  /**
   * 根据id获取班级详情
   * @param  [type] $class_id [description]
   * @return [type]           [description]
   */
  public function get_class_by_id($class_id)
  {
    $sql = "SELECT c.*,sc.name as campus_name FROM {$this->db->dbprefix}o2o_class c
     left join {$this->db->dbprefix}o2o_school_campus sc on sc.id=c.campus
     where c.id = '{$class_id}' limit 1";
    return $this->db->query($sql)->row_array();
  }

  /**
   * 获取教室
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_classroom($school_id)
  {
    $sql = "SELECT room.*,cam.name as campus_name FROM {$this->db->dbprefix}o2o_classroom room left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = room.campus_id  where room.school_id = '{$school_id}' order by id asc";
    return $this->db->query($sql)->result_array();

  }

  /**
   * 获取教室
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_classroom_by_id($room_id)
  {
    $sql = "SELECT room.* FROM {$this->db->dbprefix}o2o_classroom room where room.id = '{$room_id}' limit 1";
    return $this->db->query($sql)->row_array();

  }

  /**
   * 删除教室
   * @param  [type] $id        [description]
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function delete_room($id,$school_id)
  {
    $sql = "SELECT * FROM {$this->db->dbprefix}o2o_classroom where id = {$id} limit 1 ";
    $room = $this->db->query($sql)->row_array();
    if($room){
      if ($room['school_id']!=$school_id) {
        return -1;
      }
      return $this->db->delete('o2o_classroom',array('id' => $id ));
    }else{
      return -1;
    }
  }


  /**
   * 根据校区获取教室
   * @param  [type] $campus_id [description]
   * @return [type]            [description]
   */
  public function get_classroom_by_campus($campus_id)
  {
    $sql = "SELECT * FROM {$this->db->dbprefix}o2o_classroom where campus_id = '{$campus_id}'";
    return $this->db->query($sql)->result_array();
  }

  /**
   * 添加学校校区
   * @param [type] $data      [description]
   * @param [type] $school_id [description]
   */
  public function add_classroom($data,$school_id)
  {
    $room = array(
      'name' => $data['name'],
      'school_id' => $school_id,
      'campus_id' => $data['campus_id'],
      'total_number' => $data['total_number'],
      'add_time' => time(),
    );
    return $this->db->insert('o2o_classroom',$room);
  }

  public function edit_classroom($data,$id)
  {
    $room = array(
      'name' => $data['name'],
      'campus_id' => $data['campus_id'],
      'total_number' => $data['total_number'],
    );
    return $this->db->update('o2o_classroom',$room, array('id' => $id ));
  }


  /**
   * [get_class_by_student_and_school description]
   * @param  [type] $student_id [description]
   * @param  [type] $school_id  [description]
   * @return [type]             [description]
   */
  public function get_class_by_student($student_id,$school_id,$course_id)
  {
    $sql="SELECT cl.class_name,cl.class_num,cl.course_name,cl.lecturer_name,cl.start_time,cl.manage_teacher, cam.name as school_name,room.name as class_room FROM {$this->db->dbprefix}o2o_class_student  c_stu left join {$this->db->dbprefix}o2o_class cl on cl.id = c_stu.class_id left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = cl.campus left join {$this->db->dbprefix}o2o_classroom room on room.id = cl.classroom  WHERE c_stu.student_id = {$student_id} and cl.school_id ={$school_id} and cl.course_id= {$course_id}";
    return $this->db->query($sql)->result_array();
  }

  function get_class_list($school_id, $start_time = 0, $end_time = 0){
    $where = "school_id='{$school_id}'";
    if($this->is_manage==0){
      $where .= " and lecturer_id = {$this->user_data['teacher_id']}";
    }
    if(!empty($start_time)){
      // $start_time = strtotime($start_time);
      $where .= " and start_time >= '{$start_time}' ";
    }
    if(!empty($end_time)){
      // $end_time = strtotime($end_time) + 3600 * 24;
      $where .= " and start_time <= '{$end_time}' ";
    }
    $sql = "SELECT  * FROM {$this->db->dbprefix}o2o_class WHERE $where";
    return $this->db->query($sql)->result_array();
  }

  function get_classes_user_openid($class_id){
    $sql = "SELECT u.open_id, c.course_name FROM {$this->db->dbprefix}o2o_class_student AS c_stu ";
    $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_student AS ss ON(ss.student_id = c_stu.student_id) ";
    $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id=ss.user_id) ";
    $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_class AS c ON(c.id=c_stu.class_id) ";
    $sql .= "WHERE c_stu.class_id IN($class_id) AND u.open_id != '' AND u.open_id IS NOT NULL GROUP BY u.id";
    return $this->db->query($sql)->result_array();
  }
}
